﻿Imports System.Data
Imports System.Math
Imports System.Data.SqlClient
Imports Conexao_liion
Partial Class CustoFaturaLinhaData
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim valida As String = Session("logado")

        'lblMsg.Visible = False

        'Faz com que ao sair do aplicativo, limpe o buffer e não podendo entrar na página através do butão voltar do Browser.
        Response.Cache.SetCacheability(HttpCacheability.ServerAndNoCache)
        'Fim

        Response.Cache.SetExpires(DateTime.Now.AddSeconds(1))

        If Not Page.IsPostBack Then

            Try
                FillDDL()

                lblHolding.Text = Session("Holding").ToString
                lblCompanhia.Text = Session("Cia").ToString
                lblEstabelecimento.Text = Session("Estabelecimento").ToString
                lblFornecedor.Text = Session("Fornecedor").ToString

                lblFatura.Text = Session("strFatura2").ToString
                lblDataFatura.Text = Session("strDataFatura").ToString
                lblValorTotalFatura.Text = Session("dblValorTotalFatura")
                lblMoeda.Text = Session("strMoeda").ToString


                'lblItem.Text = Session("strCodItem").ToString '& " - " & Session("strItem").ToString
                lblQtde.Text = 0
                lblQtdeItems.Text = Session("intQtdeItems")

                Session.Add("dblValorDespesasEmbarqueItems", 0)

                LoadOrdemCompraDetail()

                If (Session("strOperacao") = "I") Then ' Incluir
                    LoadDespesasEmbarque()
                    LoadUMFornecedor()
                End If

                If (Session("strOperacao") = "C" Or Session("strOperacao") = "A" Or Session("strOperacao") = "E") Then ' Consultar | Alterar | Excluir
                    LoadData()
                End If

                LoadQtdeItems()

                If (Session("strOperacao") = "I" Or Session("strOperacao") = "A") Then ' Incluir | Alterar
                    ' Permite alterar o valor calculado considerando as despesas de embarque.
                    If ((lblDespesasEmbarque.Text > 0) And (lblQtdeItems.Text > 1)) Then
                        txtValorTotalItemFatura.Enabled = True
                    End If
                End If

                VerifyTotalValueItems()

            Catch ex As Exception
                lblMsg.Text = ex.Message
            End Try

        End If

    End Sub

    Private Sub FillDDL()

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            'theQueryString = "SELECT CodItem, CodItem + ' - ' + DscItem AS Item " & _
            '         " FROM Item" & _
            '         " WHERE CodigoDaHolding = '" & Session("strCodigoDaHoldingA").ToString & "'" & _
            '         " AND CodigoDaCia = '" & Session("strCodigoDaCiaA").ToString & "'" & _
            '         " AND CodigoDoEstabelecimento = '" & Session("strCodigoDoEstabelecimentoA").ToString & "'" & _
            '         " AND (CodFornecedor1 = '" & Session("Fornecedor").ToString & "'" & _
            '         " OR CodFornecedor2 = '" & Session("Fornecedor").ToString & "'" & _
            '         " OR CodFornecedor3 = '" & Session("Fornecedor").ToString & "')" & _
            '         " AND CodigoDaOrigemDoItem IN (1, 2)" & _
            '         " ORDER BY CodItem;"

            theQueryString = "SELECT CodItem, CodItem + ' - ' + DscItem AS Item " & _
                     " FROM Item" & _
                     " WHERE CodigoDaHolding = '" & Session("strCodigoDaHoldingA").ToString & "'" & _
                     " AND CodigoDaCia = '" & Session("strCodigoDaCiaA").ToString & "'" & _
                     " AND CodigoDoEstabelecimento = '" & Session("strCodigoDoEstabelecimentoA").ToString & "'" & _
                     " AND CodigoDaOrigemDoItem IN (1, 2)" & _
                     " ORDER BY CodItem;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlItem.DataTextField = "Item"
                ddlItem.DataValueField = "CodItem"
                ddlItem.DataSource = mydatatable
                ddlItem.DataBind()

                ddlItem.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

            If (ddlItem.Items.Count = 1) Then
                lblMsg.Text = "Fornecedor não relacionado como Fornecedor Preferencinal no Cadastro de Items."
            End If

        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDaMoeda, SiglaDaMoeda + ' - ' + NomeDaMoeda AS Moeda" & _
                         " FROM Moeda" & _
                         " WHERE CodigoDaMoeda <> 'URV'" & _
                         " ORDER BY SiglaDaMoeda;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlMoeda.DataTextField = "Moeda"
                ddlMoeda.DataValueField = "CodigoDaMoeda"
                ddlMoeda.DataSource = mydatatable
                ddlMoeda.DataBind()

                ddlMoeda.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodUnidadeMedida, SimboloUnidade + ' - ' + DscUnidadeMedida AS UnidadeMedida" & _
                         " FROM UnidadeMedida" & _
                         " ORDER BY SimboloUnidade;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlUnidadeMedida.DataTextField = "UnidadeMedida"
                ddlUnidadeMedida.DataValueField = "CodUnidadeMedida"
                ddlUnidadeMedida.DataSource = mydatatable
                ddlUnidadeMedida.DataBind()

                ddlUnidadeMedida.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


        'Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

        '    Dim mydatatable As New DataTable
        '    Dim theQueryString As String

        '    theQueryString = "SELECT CodIncoterm, CodIncoterm + ' - ' + DscIncoterm AS Incoterm" & _
        '                 " FROM Incoterm" & _
        '                 " ORDER BY DscIncoterm;"

        '    Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
        '    theSqlCommand.CommandText = theQueryString

        '    Try
        '        theSqlConnection.Open()
        '        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

        '        mydatatable.Load(theSqlDataReader)
        '        ddlIncoterm.DataTextField = "Incoterm"
        '        ddlIncoterm.DataValueField = "CodIncoterm"
        '        ddlIncoterm.DataSource = mydatatable
        '        ddlIncoterm.DataBind()

        '        ddlIncoterm.Items.Insert(0, "")

        '        theSqlDataReader.Close()
        '    Catch ex As Exception
        '        'theSqlDataReader.Close()
        '        lblMsg.Text = "Msg DB: " & ex.Message
        '    End Try

        'End Using

    End Sub

    Private Function LoadOrdemCompraDetail() As Boolean

        'Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

        '    Dim theQueryString As String = "SELECT B.DataOrdemCompra, A.LinhaOrdemCompra, A.CodNaturezaOperacao, A.QtdeOrdemCompra, A.CodUnidadeMedidaCompra, C.SimboloUnidade, A.ValorUnitarioItem, A.ValorTotalItem, A.CodigoDaMoeda, D.SiglaDaMoeda, A.CodIncoterm, A.CodCondicaoPagamento, E.DscCondicaoPagamento"

        '    theQueryString = theQueryString & _
        '             " FROM OrdemCompraDetail A, ordemcompraheader B, unidademedida C, moeda D, condicaopagamento E" & _
        '             " WHERE  A.CodigoDaHolding = B.CodigoDaHolding AND A.CodigoDaCia = B.CodigoDaCia AND A.CodigoDoEstabelecimento = B.CodigoDoEstabelecimento AND A.OrdemCompra = B.OrdemCompra AND A.CodUnidadeMedidaCompra = C.CodUnidadeMedida AND A.CodigoDaMoeda = D.CodigoDaMoeda AND A.CodCondicaoPagamento = E.CodCondicaoPagamento AND A.CodigoDaHolding = '" & Session("strCodigoDaHolding2").ToString & "'" & _
        '             " AND A.CodigoDaCia = '" & Session("strCodigoDaCia2").ToString & "'" & _
        '             " AND A.CodigoDoEstabelecimento = '" & Session("strCodigoDoEstabelecimento2").ToString & "'" & _
        '             " AND A.OrdemCompra = '" & UCase(Trim(Session("strOrdemCompra2").ToString)) & "'" & _
        '             " AND A.SeqOrdemCompra = " & Session("intSeqOrdemCompra2") & _
        '             " AND A.LinhaOrdemCompra = " & Session("intLinhaOrdemCompra") & _
        '             " AND A.CodItem = '" & Session("strCodItem").ToString & "'" & ";"

        '    Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
        '    theSqlCommand.CommandText = theQueryString

        '    Try

        '        theSqlConnection.Open()
        '        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

        '        Do While theSqlDataReader.Read()

        '            lblNaturezaOperacao.Text = theSqlDataReader!CodNaturezaOperacao
        '            Session.Add("strCodNaturezaOperacao", theSqlDataReader!CodNaturezaOperacao)

        '            Session.Add("CodUMItemOC", theSqlDataReader!CodUnidadeMedidaCompra)

        '            Session.Add("strCodMoeda", theSqlDataReader!CodigoDaMoeda)

        '            Session.Add("strCodCondicaoPagamento", theSqlDataReader!CodCondicaoPagamento)

        '            If (Session("strOperacao") = "I") Then ' Incluir

        '                txtQtdeFatura.Text = theSqlDataReader!QtdeOrdemCompra
        '                ddlUnidadeMedida.SelectedValue = theSqlDataReader!CodUnidadeMedidaCompra

        '                txtValorUnitarioItemFatura.Text = theSqlDataReader!ValorUnitarioItem
        '                lblValorTotalItem.Text = theSqlDataReader!ValorTotalItem

        '                lblValorDespesasEmbarque.Text = 0
        '                txtValorTotalItemFatura.Text = lblValorTotalItem.Text

        '                lblMoedaFatura.Text = theSqlDataReader!SiglaDaMoeda.ToString
        '                ddlIncoterm.SelectedValue = theSqlDataReader!CodIncoterm.ToString
        '                lblCondicaoPagamentoFatura.Text = theSqlDataReader!DscCondicaoPagamento.ToString

        '            End If

        '        Loop

        '        theSqlDataReader.Close()
        '    Catch ex As Exception
        '        'theSqlDataReader.Close()
        '        lblMsg.Text = "Msg DB: " & ex.Message
        '    End Try

        'End Using

    End Function

    Private Function LoadDespesasEmbarque() As Boolean

        Dim dblValorTotalItems As Double = 0
        Dim dblValorDespesasEmbarqueFatura As Double = 0

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT" & _
                         " ValorTotalItems," & _
                         " ValorDespesasEmbarque" & _
                         " FROM custofaturaheader" & _
                         " WHERE CodigoDaHolding = '" & Session("strCodigoDaHoldingA").ToString & "'" & _
                         " AND CodigoDaCia = '" & Session("strCodigoDaCiaA").ToString & "'" & _
                         " AND CodigoDoEstabelecimento = '" & Session("strCodigoDoEstabelecimentoA").ToString & "'" & _
                         " AND Fatura = '" & UCase(Trim(Session("strFatura2").ToString)) & "'" & ";"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try

                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                Do While theSqlDataReader.Read()

                    dblValorTotalItems = theSqlDataReader!ValorTotalItems
                    dblValorDespesasEmbarqueFatura = theSqlDataReader!ValorDespesasEmbarque
                    lblDespesasEmbarque.Text = theSqlDataReader!ValorDespesasEmbarque
                    lblValorTotalItem.Text = theSqlDataReader!ValorTotalItems
                    lblValorDespesasEmbarque.Text = theSqlDataReader!ValorDespesasEmbarque
                    If Not (dblValorDespesasEmbarqueFatura = 0) Then
                        'lblValorDespesasEmbarque.Text = (dblValorDespesasEmbarqueFatura / dblValorTotalItems) * lblValorTotalItem.Text
                        'lblValorDespesasEmbarque.Text = Round(CDbl(lblValorDespesasEmbarque.Text), 2)
                    End If

                    'txtValorTotalItemFatura.Text = (CDbl(lblValorTotalItem.Text) + CDbl(lblValorDespesasEmbarque.Text))

                Loop

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Private Sub LoadUMFornecedor()

        'Session.Add("UnidadesPorCaixa", 0)
        'Session.Add("CodUnidadeMedidaCompraFornecedor", -1)

        'Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

        '    Dim theQueryString As String = "SELECT UnidadesPorCaixa, CodUnidadeMedidaCompraFornecedor1," & _
        '                 " CodUnidadeMedidaCompraFornecedor2, CodUnidadeMedidaCompraFornecedor3" & _
        '                 " FROM Item" & _
        '                 " WHERE CodigoDaHolding = '" & Session("strCodigoDaHoldingA").ToString & "'" & _
        '                 " AND CodigoDaCia = '" & Session("strCodigoDaCiaA").ToString & "'" & _
        '                 " AND CodigoDoEstabelecimento = '" & Session("strCodigoDoEstabelecimentoA").ToString & "'" & _
        '                 " AND (CodFornecedor1 = '" & Session("Fornecedor").ToString & "'" & _
        '                 " OR CodFornecedor2 = '" & Session("Fornecedor").ToString & "'" & _
        '                 " OR CodFornecedor3 = '" & Session("Fornecedor").ToString & "')" & _
        '                 " AND CodItem = '" & Session("strCodItem").ToString & "'" & ";"

        '    Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
        '    theSqlCommand.CommandText = theQueryString

        '    Try

        '        theSqlConnection.Open()
        '        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

        '        Do While theSqlDataReader.Read()

        '            If Not (IsDBNull(theSqlDataReader(0)) = True) Then
        '                Session.Add("UnidadesPorCaixa", theSqlDataReader!UnidadesPorCaixa)
        '            End If

        '            If Not (IsDBNull(theSqlDataReader(1)) = True) Then
        '                Session.Add("CodUnidadeMedidaCompraFornecedor", theSqlDataReader!CodUnidadeMedidaCompraFornecedor1)
        '            Else
        '                If Not (IsDBNull(theSqlDataReader(2)) = True) Then
        '                    Session.Add("CodUnidadeMedidaCompraFornecedor", theSqlDataReader!CodUnidadeMedidaCompraFornecedor2)
        '                Else
        '                    If Not (IsDBNull(theSqlDataReader(3)) = True) Then
        '                        Session.Add("CodUnidadeMedidaCompraFornecedor", theSqlDataReader!CodUnidadeMedidaCompraFornecedor3)
        '                    End If
        '                End If
        '            End If

        '        Loop

        '        theSqlDataReader.Close()
        '    Catch ex As Exception
        '        'theSqlDataReader.Close()
        '        lblMsg.Text = "Msg DB: " & ex.Message
        '    End Try

        'End Using

        'ddlUnidadeMedida.SelectedValue = Session("CodUnidadeMedidaCompraFornecedor")

        'If (Session("CodUMItemOC") <> Session("CodUnidadeMedidaCompraFornecedor")) Then
        '    'txtQtdeFatura.Text = CDbl(lblQtdeOC.Text) * CDbl(Session("UnidadesPorCaixa"))
        '    ConvertUM()
        'End If

        ''lblUMItemOC.ToolTip = myReader.GetString("CodUnidadeMedidaCompra")

    End Sub

    Private Sub ConvertUM()


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT Operacao, Fator" & _
                         " FROM ConversaoUnidadeMedida" & _
                         " WHERE CodUnidadeMedidaA = " & Session("CodUMItemOC") & _
                         " AND CodUnidadeMedidaB = " & Session("CodUnidadeMedidaCompraFornecedor") & ";"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try

                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                Do While theSqlDataReader.Read()

                    If (IsDBNull(theSqlDataReader!Fator) = False) Then
                        Select Case theSqlDataReader!Operacao.ToString
                            Case "ADD"
                                '    txtQtdeFatura.Text = Round(CDbl(lblQtdeOC.Text) + CDbl(theSqlDataReader!Fator), 2)
                                'Case "SUB"
                                '    txtQtdeFatura.Text = Round(CDbl(lblQtdeOC.Text) - CDbl(theSqlDataReader!Fator), 2)
                                'Case "MUL"
                                '    txtQtdeFatura.Text = CDbl(lblQtdeOC.Text) * CDbl(theSqlDataReader!Fator)
                                'Case "DIV"
                                '    txtQtdeFatura.Text = CDbl(lblQtdeOC.Text) / CDbl(theSqlDataReader!Fator)
                        End Select
                    Else
                        Select Case theSqlDataReader!Operacao.ToString
                            Case "ADD"
                                '    txtQtdeFatura.Text = Round(CDbl(lblQtdeOC.Text) + CDbl(Session("UnidadesPorCaixa")), 2)
                                'Case "SUB"
                                '    txtQtdeFatura.Text = Round(CDbl(lblQtdeOC.Text) - CDbl(Session("UnidadesPorCaixa")), 2)
                                'Case "MUL"
                                '    txtQtdeFatura.Text = Round(CDbl(lblQtdeOC.Text) * CDbl(Session("UnidadesPorCaixa")), 2)
                                'Case "DIV"
                                '    txtQtdeFatura.Text = Round(CDbl(lblQtdeOC.Text) / CDbl(Session("UnidadesPorCaixa")), 2)
                        End Select
                    End If

                    txtValorUnitarioItemFatura.Text = Round((lblValorTotalItem.Text / txtQtdeFatura.Text), 4)

                Loop

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    Private Sub LoadData()

        lblLinhaFatura.Text = Session("intLinhaFatura")

        If (Session("strOperacao").ToString = "C") Then
            btnGravar.Enabled = False
        End If

        If (Session("strOperacao").ToString = "C" Or Session("strOperacao").ToString = "E") Then ' Consultar | Excluir

            txtQtdeFatura.Enabled = False
            ddlUnidadeMedida.Enabled = False
            txtValorUnitarioItemFatura.Enabled = False
            'ddlIncoterm.Enabled = False

        End If


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT A.QtdeFatura, A.CodUnidadeMedidaCompra, A.Adicao, A.OrdemCompra, A.CodItem, A.PesoLiquido, A.ValorUnitarioItem, A.ValorTotalItem, A.ValorDespesasEmbarque, A.ValorTotalItemFatura, A.CodigoDaMoeda, B.SiglaDaMoeda" & _
                         " FROM custofaturadetail A, moeda B" & _
                         " WHERE A.CodigoDaMoeda = B.CodigoDaMoeda AND A.CodigoDaHolding = '" & Session("strCodigoDaHoldingA").ToString & "'" & _
                         " AND A.CodigoDaCia = '" & Session("strCodigoDaCiaA").ToString & "'" & _
                         " AND A.CodigoDoEstabelecimento = '" & Session("strCodigoDoEstabelecimentoA").ToString & "'" & _
                         " AND A.Fatura = '" & UCase(Trim(Session("strFatura2").ToString)) & "'" & _
                         " AND A.LinhaFatura = " & Session("intLinhaFatura") & ";"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try

                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                Do While theSqlDataReader.Read()

                    If Not (IsDBNull(theSqlDataReader!OrdemCompra) = True) Then
                        txtOrdemCompra.Text = theSqlDataReader!OrdemCompra
                    End If

                    If Not (IsDBNull(theSqlDataReader!Adicao) = True) Then
                        txtAdicao.Text = theSqlDataReader!Adicao
                    End If


                    ddlItem.SelectedValue = theSqlDataReader!CodItem
                    txtQtdeFatura.Text = theSqlDataReader!QtdeFatura
                    ddlUnidadeMedida.SelectedValue = theSqlDataReader!CodUnidadeMedidaCompra
                    txtPesoLiquido.Text = theSqlDataReader!PesoLiquido
                    txtValorUnitarioItemFatura.Text = theSqlDataReader!ValorUnitarioItem
                    lblValorTotalItem.Text = theSqlDataReader!ValorTotalItem
                    lblValorDespesasEmbarque.Text = theSqlDataReader!ValorDespesasEmbarque
                    txtValorTotalItemFatura.Text = theSqlDataReader!ValorTotalItemFatura
                    ddlMoeda.SelectedValue = theSqlDataReader!CodigoDaMoeda
                    txtPesoLiquido.Text = theSqlDataReader!PesoLiquido
                    'ddlIncoterm.SelectedValue = theSqlDataReader!CodIncoterm.ToString
                    'lblCondicaoPagamentoFatura.Text = theSqlDataReader!DscCondicaoPagamento.ToString

                Loop

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    Private Sub LoadQtdeItems()

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT" & _
                         " COUNT(CodItem) AS QtdeItems" & _
                         " FROM custofaturadetail" & _
                         " WHERE CodigoDaHolding = '" & Session("strCodigoDaHoldingA").ToString & "'" & _
                         " AND CodigoDaCia = '" & Session("strCodigoDaCiaA").ToString & "'" & _
                         " AND CodigoDoEstabelecimento = '" & Session("strCodigoDoEstabelecimentoA").ToString & "'" & _
                         " AND Fatura = '" & UCase(Trim(Session("strFatura2").ToString)) & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try

                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                Do While theSqlDataReader.Read()
                    lblQtde.Text = theSqlDataReader!QtdeItems
                Loop

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    Private Sub VerifyTotalValueItems()

        Dim dlValorTotalItems As Double = 0

        lblValorTotalItems.Text = 0

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "SELECT" & _
                         " SUM(ValorDespesasEmbarque) AS SUM_ValorDespesasEmbarque, SUM(ValorTotalItemFatura) AS SUM_ValorTotalItemFatura" & _
                         " FROM custofaturadetail" & _
                         " WHERE CodigoDaHolding = '" & Session("strCodigoDaHoldingA").ToString & "'" & _
                         " AND CodigoDaCia = '" & Session("strCodigoDaCiaA").ToString & "'" & _
                         " AND CodigoDoEstabelecimento = '" & Session("strCodigoDoEstabelecimentoA").ToString & "'" & _
                         " AND Fatura = '" & Session("strFatura2").ToString & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.HasRows = True) Then
                    Do While theSqlDataReader.Read()
                        Session.Add("dblValorDespesasEmbarqueItems", theSqlDataReader!SUM_ValorDespesasEmbarque)
                        If Not (IsDBNull(theSqlDataReader!SUM_ValorTotalItemFatura) = True) Then
                            lblValorTotalItems.Text = theSqlDataReader!SUM_ValorTotalItemFatura
                        Else
                            lblValorTotalItems.Text = "0"
                        End If
                    Loop

                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    Protected Sub txtQtdeFatura_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtQtdeFatura.TextChanged

        CalculateValueItem()

    End Sub

    Protected Sub txtValorUnitarioItemFatura_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtValorUnitarioItemFatura.TextChanged

        'CalculateValueItem()

    End Sub

    Private Sub CalculateValueItem()

        Try
            lblMsg.Text = ""

            txtValorUnitarioItemFatura.Text = ""

            If (Trim(txtQtdeFatura.Text) = "") Then
                Exit Sub
            End If

            If Not (IsNumeric(txtQtdeFatura.Text) = True) Then
                Exit Sub
            End If

            txtValorUnitarioItemFatura.Text = (lblValorTotalItem.Text / txtQtdeFatura.Text)
            txtValorUnitarioItemFatura.Text = Round(CDbl(txtValorUnitarioItemFatura.Text), 4)

        Catch ex As Exception
            lblMsg.Text = ex.Message
        End Try

    End Sub

    Protected Sub btnGravar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGravar.Click

        Dim ConnectionTest As String
        ConnectionTest = "A"

        lblMsg.Text = ""


        If (Session("strOperacao") = "I") Then ' Incluir

            If Not (DataValid() = True) Then
                Exit Sub
            End If

            If Not (CreateSequence(ConnectionTest) = True) Then
                Exit Sub
            End If

            If Not (AddRecord(ConnectionTest) = True) Then
                Exit Sub
            End If

            If Not (EditFatura(ConnectionTest) = True) Then
                Exit Sub
            End If

            'If Not (EditOrdemCompraDetail(ConnectionTest, 4) = True) Then ' Linha da Ordem de Compra como Faturada
            '    Exit Sub
            'End If

            'If Not (EditOrdemCompraHeader(ConnectionTest) = True) Then
            '    Exit Sub
            'End If

        ElseIf (Session("strOperacao") = "A") Then ' Alterar

            If Not (DataValid() = True) Then
                Exit Sub
            End If

            If Not (EditRecord(ConnectionTest) = True) Then
                Exit Sub
            End If

        ElseIf (Session("strOperacao") = "E") Then ' Excluir

            'If (VerifyData(ConnectionTest) = True) Then
            '    Exit Sub
            'End If

            If Not (DeleteRecord(ConnectionTest) = True) Then
                Exit Sub
            End If

            'If Not (EditFatura(ConnectionTest) = True) Then
            '    Exit Sub
            'End If

            'If Not (EditOrdemCompraDetail(ConnectionTest, 3) = True) Then ' Linha da Ordem de Compra como Pedido
            '    Exit Sub
            'End If

            'If Not (EditOrdemCompraHeader(ConnectionTest) = True) Then
            '    Exit Sub
            'End If

        End If

        lblMsg.ForeColor = Drawing.Color.Green
        lblMsg.Text = "Operação realizada com sucesso. Clique no botão 'OK'"

        btnGravar.Enabled = False
        btnVoltar.Text = "OK"

    End Sub

    Private Function DataValid() As Boolean

        Dim dblValorTotalItems As Double = 0
        Dim dblDespesasItems As Double = 0
        Dim dblValorTotalFatura As Double = 0

        DataValid = False

        If (Trim(txtQtdeFatura.Text) = "") Then
            txtValorUnitarioItemFatura.Text = ""
            lblMsg.Text = "Quantidade deve ser preenchida."
            Exit Function
        End If

        If Not IsNumeric(txtQtdeFatura.Text) = True Then
            txtValorUnitarioItemFatura.Text = ""
            lblMsg.Text = "Quantidade deve ser numérica."
            Exit Function
        End If

        If (ddlUnidadeMedida.Text = "") Then
            lblMsg.Text = "Unidade de medida deve ser selecionada."
            Exit Function
        End If

        If (Trim(txtValorUnitarioItemFatura.Text) = "") Then
            CalculateValueItem()
            'txtValorUnitarioItem.Text = (txtValorTotalItem.Text / txtQtdeOC.Text)
            'txtValorUnitarioItemFatura.Text = Round(CDbl(txtValorUnitarioItemFatura.Text), 4)
        End If

        If (Session("strOperacao") = "I" Or Session("strOperacao") = "A") Then ' Incluir | Alterar

            If ((lblValorDespesasEmbarque.Text > 0) And (lblQtdeItems.Text > 1)) Then

                If (Trim(txtValorTotalItemFatura.Text) = "") Then
                    lblMsg.Text = "Valor total do item deve ser preenchido."
                    Exit Function
                End If

                If Not IsNumeric(txtValorTotalItemFatura.Text) = True Then
                    lblMsg.Text = "Valor total do item deve ser numérico."
                    Exit Function
                End If

                'If (CDbl(txtValorTotalItemFatura.Text) < CDbl(lblValorTotalItemOC.Text)) Then
                '    lblMsg.Text = "Valor total do item deve ser maior que o Valor total da Linha da Ordem de Compra."
                '    Exit Function
                'End If

                lblValorDespesasEmbarque.Text = (CDbl(txtValorTotalItemFatura.Text) - CDbl(lblValorTotalItem.Text))
                lblValorDespesasEmbarque.Text = Round(CDbl(lblValorDespesasEmbarque.Text), 2)

                If Not (IsDBNull(Session("dblValorDespesasEmbarqueItems")) = True) Then
                    dblDespesasItems = (CDbl(lblValorDespesasEmbarque.Text) + CDbl(Session("dblValorDespesasEmbarqueItems")))
                End If

                If (dblDespesasItems > CDbl(lblDespesasEmbarque.Text)) Then
                    lblMsg.Text = "Valor total das despesas (" & dblDespesasItems & "), maior que o valor total da despesa da Fatura (" & CDbl(lblDespesasEmbarque.Text) & ")."
                    Exit Function
                End If

            End If

            'If (ddlIncoterm.Text = "") Then
            '    lblMsg.Text = "Incoterm da Linha da Fatura deve ser selecionada."
            '    Exit Function
            'End If

        End If

        If (Session("strOperacao") = "I") Then
            dblValorTotalItems = (CDbl(lblValorTotalItems.Text) + CDbl(txtValorTotalItemFatura.Text))
        Else
            dblValorTotalItems = txtValorTotalItemFatura.Text
        End If
        dblValorTotalFatura = lblValorTotalFatura.Text
        If (lblQtdeItems.Text = 1) Then
            If (dblValorTotalItems <> lblValorTotalFatura.Text) Then
                lblMsg.Text = "Valor total do item (" & dblValorTotalItems & "), deve ser igual ao Valor total da Fatura (" & lblValorTotalFatura.Text & ")."
                Exit Function
            End If
        End If

        If (lblQtdeItems.Text > 1) And (lblQtde.Text > 0) Then
            If (lblQtdeItems.Text - lblQtde.Text = 1) Then
                If (Format(dblValorTotalItems, "#.##") <> CDbl(1 * Format(dblValorTotalFatura, "#.##"))) Then
                    lblMsg.Text = "Valor total dos items (" & dblValorTotalItems & "), deve ser igual ao Valor total da Fatura (" & lblValorTotalFatura.Text & ")."
                    Exit Function
                End If
            End If
        End If

        If (Format(dblValorTotalItems, "#.##") > (1 * Format(dblValorTotalFatura, "#.##"))) Then
            lblMsg.Text = "Valor total dos items (" & dblValorTotalItems & "), maior que o Valor total da Fatura (" & lblValorTotalFatura.Text & ")."
            Exit Function
        End If

        DataValid = True

    End Function

    Private Function CreateSequence(ByVal strConn As String) As Boolean

        CreateSequence = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "SELECT MAX(LinhaFatura) AS MAX_LinhaFatura" & _
                     " FROM custofaturadetail" & _
                     " WHERE CodigoDaHolding = '" & Session("strCodigoDaHoldingA").ToString & "'" & _
                     " AND CodigoDaCia = '" & Session("strCodigoDaCiaA").ToString & "'" & _
                     " AND CodigoDoEstabelecimento = '" & Session("strCodigoDoEstabelecimentoA").ToString & "'" & _
                     " AND Fatura = '" & Session("strFatura2").ToString & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.HasRows = True) Then
                    Do While theSqlDataReader.Read()
                        If Not (IsDBNull(theSqlDataReader(0)) = True) Then
                            lblLinhaFatura.Text = (theSqlDataReader(0) + 1)
                        Else
                            lblLinhaFatura.Text = 1
                        End If
                    Loop
                Else
                    lblLinhaFatura.Text = 1
                End If

                CreateSequence = True

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Private Function AddRecord(ByVal strConn As String) As Boolean

        Dim intSituacaoFatura As Integer
        Dim nDiaCreated As String
        Dim nMesCreated As String
        Dim nAnoCreated As String
        Dim nLastUpdated As String

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            'Dim dAnoVigente As String
            'Dim i As Long

            Dim theQueryString As String = "SELECT CONVERT(CHAR(10), GETDATE(), 103) AS DATADOSERVIDOR"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                Do While theSqlDataReader.Read()
                    Session("dDTAno") = Trim(theSqlDataReader(0))
                Loop
                theSqlDataReader.Close()
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        End Using

        nDiaCreated = Mid(Session("dDTAno"), 1, 2)
        nMesCreated = Mid(Session("dDTAno"), 4, 2)
        nAnoCreated = Mid(Session("dDTAno"), 7, 4)
        nLastUpdated = nDiaCreated & "/" & nMesCreated & "/" & nAnoCreated

        If (IsDate(nLastUpdated)) Then
            nLastUpdated = "'" & FormatMediumDate(nLastUpdated) & "'"
        Else
            nLastUpdated = "Null"
        End If

        AddRecord = False

        intSituacaoFatura = 3 ' Faturada

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)


            txtQtdeFatura.Text = Replace(txtQtdeFatura.Text, ",", ".")
            txtValorUnitarioItemFatura.Text = Replace(txtValorUnitarioItemFatura.Text, ",", ".")

            lblValorTotalItem.Text = Replace(lblValorTotalItem.Text, ",", ".")
            lblValorDespesasEmbarque.Text = Replace(lblValorDespesasEmbarque.Text, ",", ".")
            txtValorTotalItemFatura.Text = Replace(txtValorTotalItemFatura.Text, ",", ".")
            txtPesoLiquido.Text = Replace(txtPesoLiquido.Text, ",", ".")

            Dim theQueryString As String
            theQueryString = "INSERT INTO custofaturadetail" & _
                     " (" & _
                     " CodigoDaHolding," & _
                     " CodigoDaCia," & _
                     " CodigoDoEstabelecimento," & _
                     " Fatura," & _
                     " LinhaFatura," & _
                     " Adicao," & _
                     " OrdemCompra," & _
                     " CodItem," & _
                     " QtdeFatura," & _
                     " CodUnidadeMedidaCompra," & _
                     " PesoLiquido," & _
                     " ValorUnitarioItem," & _
                     " ValorTotalItem," & _
                     " ValorDespesasEmbarque," & _
                     " ValorTotalItemFatura," & _
                     " CodSituacaoFatura," & _
                     " CodigoDaMoeda," & _
                     " CreateUser," & _
                     " Created" & _
                     " )"

            theQueryString = theQueryString & _
                     " VALUES " & _
                     " (" & _
                     " '" & Session("strCodigoDaHoldingA").ToString & "'," & _
                     " '" & Session("strCodigoDaCiaA").ToString & "'," & _
                     " '" & Session("strCodigoDoEstabelecimentoA").ToString & "'," & _
                     " '" & lblFatura.Text & "'," & _
                     " " & lblLinhaFatura.Text & "," & _
                     " " & txtAdicao.Text & "," & _
                     " '" & txtOrdemCompra.Text & "'," & _
                     " '" & ddlItem.SelectedValue & "'," & _
                     " " & txtQtdeFatura.Text & "," & _
                     " " & ddlUnidadeMedida.SelectedValue & "," & _
                     " " & txtPesoLiquido.Text & "," & _
                     " " & txtValorUnitarioItemFatura.Text & "," & _
                     " " & lblValorTotalItem.Text & "," & _
                     " " & lblValorDespesasEmbarque.Text & "," & _
                     " " & txtValorTotalItemFatura.Text & "," & _
                     " " & intSituacaoFatura & "," & _
                     " '" & ddlMoeda.SelectedValue & "'," & _
                     " '" & Session("sNM_USERNICKNAME").ToString & "'," & _
                     " " & nLastUpdated & "" & _
                     " );"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
                AddRecord = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Private Function EditRecord(ByVal strConn As String) As Boolean

        Dim nDiaCreated As String
        Dim nMesCreated As String
        Dim nAnoCreated As String
        Dim nLastUpdated As String

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            'Dim dAnoVigente As String
            'Dim i As Long

            Dim theQueryString As String = "SELECT CONVERT(CHAR(10), GETDATE(), 103) AS DATADOSERVIDOR"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                Do While theSqlDataReader.Read()
                    Session("dDTAno") = Trim(theSqlDataReader(0))
                Loop
                theSqlDataReader.Close()
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        End Using

        nDiaCreated = Mid(Session("dDTAno"), 1, 2)
        nMesCreated = Mid(Session("dDTAno"), 4, 2)
        nAnoCreated = Mid(Session("dDTAno"), 7, 4)
        nLastUpdated = nDiaCreated & "/" & nMesCreated & "/" & nAnoCreated

        If (IsDate(nLastUpdated)) Then
            nLastUpdated = "'" & FormatMediumDate(nLastUpdated) & "'"
        Else
            nLastUpdated = "Null"
        End If

        EditRecord = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            txtQtdeFatura.Text = Replace(txtQtdeFatura.Text, ",", ".")
            txtValorUnitarioItemFatura.Text = Replace(txtValorUnitarioItemFatura.Text, ",", ".")
            txtPesoLiquido.Text = Replace(txtPesoLiquido.Text, ",", ".")

            Dim theQueryString As String
            theQueryString = "UPDATE custofaturadetail SET" & _
                     " OrdemCompra = '" & txtOrdemCompra.Text & "'," & _
                     " Adicao = " & txtAdicao.Text & "," & _
                     " QtdeFatura = " & txtQtdeFatura.Text & "," & _
                     " CodUnidadeMedidaCompra = " & ddlUnidadeMedida.SelectedValue & "," & _
                     " ValorUnitarioItem = " & txtValorUnitarioItemFatura.Text & "," & _
                     " CodigoDaMoeda = '" & ddlMoeda.SelectedValue & "'," & _
                     " PesoLiquido = " & txtPesoLiquido.Text & "," & _
                     " LastUser = '" & Session("sNM_USERNICKNAME").ToString & "'," & _
                     " LastUpdated = " & nLastUpdated & "" & _
                     " WHERE CodigoDaHolding = '" & Session("strCodigoDaHoldingA").ToString & "'" & _
                     " AND CodigoDaCia = '" & Session("strCodigoDaCiaA").ToString & "'" & _
                     " AND CodigoDoEstabelecimento = '" & Session("strCodigoDoEstabelecimentoA").ToString & "'" & _
                     " AND Fatura = '" & Session("strFatura2").ToString & "'" & _
                     " AND LinhaFatura = " & lblLinhaFatura.Text & ";"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
                EditRecord = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Private Function EditFatura(ByVal strConn As String) As Boolean


        Dim nDiaCreated As String
        Dim nMesCreated As String
        Dim nAnoCreated As String
        Dim nLastUpdated As String

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            'Dim dAnoVigente As String
            'Dim i As Long

            Dim theQueryString As String = "SELECT CONVERT(CHAR(10), GETDATE(), 103) AS DATADOSERVIDOR"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                Do While theSqlDataReader.Read()
                    Session("dDTAno") = Trim(theSqlDataReader(0))
                Loop
                theSqlDataReader.Close()
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        End Using

        nDiaCreated = Mid(Session("dDTAno"), 1, 2)
        nMesCreated = Mid(Session("dDTAno"), 4, 2)
        nAnoCreated = Mid(Session("dDTAno"), 7, 4)
        nLastUpdated = nDiaCreated & "/" & nMesCreated & "/" & nAnoCreated

        If (IsDate(nLastUpdated)) Then
            nLastUpdated = "'" & FormatMediumDate(nLastUpdated) & "'"
        Else
            nLastUpdated = "Null"
        End If

        EditFatura = False

        VerifyTotalValueItems()

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "UPDATE custofaturaheader SET" & _
                     " CodSituacaoFatura = " & IIf(CDbl(lblValorTotalFatura.Text) = CDbl(lblValorTotalItems.Text), 3, 2) & "," & _
                     " LastUser = '" & Session("sNM_USERNICKNAME").ToString & "'," & _
                     " LastUpdated = " & nLastUpdated & "" & _
                     " WHERE CodigoDaHolding = '" & Session("strCodigoDaHoldingA").ToString & "'" & _
                     " AND CodigoDaCia = '" & Session("strCodigoDaCiaA").ToString & "'" & _
                     " AND CodigoDoEstabelecimento = '" & Session("strCodigoDoEstabelecimentoA").ToString & "'" & _
                     " AND Fatura = '" & Session("strFatura2").ToString & "'" & ";"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
                EditFatura = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Private Function EditOrdemCompraDetail(ByVal strConn As String, ByVal intSituacaoOrdemCompra As Integer) As Boolean

        'Dim nDiaCreated As String
        'Dim nMesCreated As String
        'Dim nAnoCreated As String
        'Dim nLastUpdated As String

        'Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

        '    'Dim dAnoVigente As String
        '    'Dim i As Long

        '    Dim theQueryString As String = "SELECT CONVERT(CHAR(10), GETDATE(), 103) AS DATADOSERVIDOR"
        '    Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
        '    theSqlCommand.CommandText = theQueryString
        '    Try
        '        theSqlConnection.Open()
        '        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
        '        Do While theSqlDataReader.Read()
        '            Session("dDTAno") = Trim(theSqlDataReader(0))
        '        Loop
        '        theSqlDataReader.Close()
        '    Catch ex As Exception
        '        Console.WriteLine(ex.Message)
        '    End Try

        'End Using

        'nDiaCreated = Mid(Session("dDTAno"), 1, 2)
        'nMesCreated = Mid(Session("dDTAno"), 4, 2)
        'nAnoCreated = Mid(Session("dDTAno"), 7, 4)
        'nLastUpdated = nDiaCreated & "/" & nMesCreated & "/" & nAnoCreated

        'If (IsDate(nLastUpdated)) Then
        '    nLastUpdated = "'" & FormatMediumDate(nLastUpdated) & "'"
        'Else
        '    nLastUpdated = "Null"
        'End If

        'EditOrdemCompraDetail = False

        'Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

        '    Dim theQueryString As String
        '    theQueryString = "UPDATE OrdemCompraDetail SET" & _
        '             " CodSituacaoOrdemCompra = " & intSituacaoOrdemCompra & "," & _
        '             " LastUser = '" & Session("sNM_USERNICKNAME").ToString & "'," & _
        '             " LastUpdated = " & nLastUpdated & "" & _
        '             " WHERE CodigoDaHolding = '" & Session("strCodigoDaHolding2").ToString & "'" & _
        '             " AND CodigoDaCia = '" & Session("strCodigoDaCia2").ToString & "'" & _
        '             " AND CodigoDoEstabelecimento = '" & Session("strCodigoDoEstabelecimento2").ToString & "'" & _
        '             " AND OrdemCompra = '" & lblOrdemCompra.Text & "'" & _
        '             " AND SeqOrdemCompra = " & lblSeqOC.Text & _
        '             " AND LinhaOrdemCompra = " & lblLinhaOC.Text & ";"

        '    Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
        '    theSqlCommand.CommandText = theQueryString
        '    Try
        '        theSqlConnection.Open()
        '        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
        '        theSqlDataReader.Close()
        '        EditOrdemCompraDetail = True
        '    Catch ex As Exception
        '        'theSqlDataReader.Close()
        '        lblMsg.Text = "Msg DB: " & ex.Message
        '    End Try

        'End Using

    End Function

    Private Function EditOrdemCompraHeader(ByVal strConn As String) As Boolean

        'Dim intSituacaoOrdemCompra As Integer = 0
        'Dim nDiaCreated As String
        'Dim nMesCreated As String
        'Dim nAnoCreated As String
        'Dim nLastUpdated As String

        'Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

        '    'Dim dAnoVigente As String
        '    'Dim i As Long

        '    Dim theQueryString As String = "SELECT CONVERT(CHAR(10), GETDATE(), 103) AS DATADOSERVIDOR"
        '    Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
        '    theSqlCommand.CommandText = theQueryString
        '    Try
        '        theSqlConnection.Open()
        '        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
        '        Do While theSqlDataReader.Read()
        '            Session("dDTAno") = Trim(theSqlDataReader(0))
        '        Loop
        '        theSqlDataReader.Close()
        '    Catch ex As Exception
        '        Console.WriteLine(ex.Message)
        '    End Try

        'End Using

        'nDiaCreated = Mid(Session("dDTAno"), 1, 2)
        'nMesCreated = Mid(Session("dDTAno"), 4, 2)
        'nAnoCreated = Mid(Session("dDTAno"), 7, 4)
        'nLastUpdated = nDiaCreated & "/" & nMesCreated & "/" & nAnoCreated

        'If (IsDate(nLastUpdated)) Then
        '    nLastUpdated = "'" & FormatMediumDate(nLastUpdated) & "'"
        'Else
        '    nLastUpdated = "Null"
        'End If


        'EditOrdemCompraHeader = False

        'Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

        '    Dim theQueryString As String
        '    theQueryString = "SELECT CodSituacaoOrdemCompra" & _
        '                 " FROM OrdemCompraDetail" & _
        '                 " WHERE CodigoDaHolding = '" & Session("strCodigoDaHolding2").ToString & "'" & _
        '                 " AND CodigoDaCia = '" & Session("strCodigoDaCia2").ToString & "'" & _
        '                 " AND CodigoDoEstabelecimento = '" & Session("strCodigoDoEstabelecimento2").ToString & "'" & _
        '                 " AND OrdemCompra = '" & lblOrdemCompra.Text & "'" & _
        '                 " AND SeqOrdemCompra = " & lblSeqOC.Text & ";"

        '    Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
        '    theSqlCommand.CommandText = theQueryString

        '    Try
        '        theSqlConnection.Open()
        '        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

        '        If (theSqlDataReader.HasRows = True) Then
        '            intSituacaoOrdemCompra = 4 ' Faturada
        '            Do While theSqlDataReader.Read()
        '                If (theSqlDataReader!CodSituacaoOrdemCompra = 3) Then ' Pedido
        '                    intSituacaoOrdemCompra = 3
        '                End If
        '            Loop
        '        Else
        '            EditOrdemCompraHeader = True
        '        End If

        '        theSqlDataReader.Close()
        '    Catch ex As Exception
        '        'theSqlDataReader.Close()
        '        lblMsg.Text = "Msg DB: " & ex.Message
        '    End Try

        'End Using

        'If (intSituacaoOrdemCompra <> 0) Then ' Alterar

        '    Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

        '        Dim theQueryString As String
        '        theQueryString = "UPDATE OrdemCompraHeader SET" & _
        '                         " CodSituacaoOrdemCompra = " & intSituacaoOrdemCompra & "," & _
        '                         " LastUser = '" & Session("sNM_USERNICKNAME").ToString & "'," & _
        '                         " LastUpdated = " & nLastUpdated & "" & _
        '                         " WHERE CodigoDaHolding = '" & Session("strCodigoDaHolding2").ToString & "'" & _
        '                         " AND CodigoDaCia = '" & Session("strCodigoDaCia2").ToString & "'" & _
        '                         " AND CodigoDoEstabelecimento = '" & Session("strCodigoDoEstabelecimento2").ToString & "'" & _
        '                         " AND OrdemCompra = '" & lblOrdemCompra.Text & "'" & _
        '                         " AND SeqOrdemCompra = " & lblSeqOC.Text & ";"

        '        Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
        '        theSqlCommand.CommandText = theQueryString
        '        Try
        '            theSqlConnection.Open()
        '            Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
        '            theSqlDataReader.Close()
        '            EditOrdemCompraHeader = True
        '        Catch ex As Exception
        '            'theSqlDataReader.Close()
        '            lblMsg.Text = "Msg DB: " & ex.Message
        '        End Try

        '    End Using

        'End If

    End Function

    Private Function VerifyData(ByVal strConn As String) As Boolean

        'VerifyData = False

        'Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

        '    Dim theQueryString As String
        '    theQueryString = "SELECT DI" & _
        '             " FROM DIDetail" & _
        '             " WHERE CodigoDaHolding = '" & Session("strCodigoDaHolding2").ToString & "'" & _
        '             " AND CodigoDaCia = '" & Session("strCodigoDaCia2").ToString & "'" & _
        '             " AND CodigoDoEstabelecimento = '" & Session("strCodigoDoEstabelecimento2").ToString & "'" & _
        '             " AND Fatura = '" & Session("strFatura2").ToString & "'" & _
        '             " AND LinhaFatura = " & lblLinhaFatura.Text & ";"

        '    Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
        '    theSqlCommand.CommandText = theQueryString

        '    Try
        '        theSqlConnection.Open()
        '        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

        '        If (theSqlDataReader.HasRows = True) Then
        VerifyData = True
        '            Do While theSqlDataReader.Read()
        '                lblMsg.Text = "Exclusão não permitida, existe relação do Item da Fatura " & Session("strFatura2").ToString & " com a DI " & theSqlDataReader(0).ToString & "."
        '                lblValorTotalItems.Text = theSqlDataReader(0)
        '            Loop
        '        End If
        '        theSqlDataReader.Close()
        '    Catch ex As Exception
        '        'theSqlDataReader.Close()
        '        lblMsg.Text = "Msg DB: " & ex.Message
        '    End Try

        'End Using

    End Function

    Private Function DeleteRecord(ByVal strConn As String) As Boolean

        DeleteRecord = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "DELETE" & _
                     " FROM custoFaturaDetail" & _
                     " WHERE CodigoDaHolding = '" & Session("strCodigoDaHoldingA").ToString & "'" & _
                     " AND CodigoDaCia = '" & Session("strCodigoDaCiaA").ToString & "'" & _
                     " AND CodigoDoEstabelecimento = '" & Session("strCodigoDoEstabelecimentoA").ToString & "'" & _
                     " AND Fatura = '" & Session("strFatura2").ToString & "'" & _
                     " AND LinhaFatura = " & lblLinhaFatura.Text & ";"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
                DeleteRecord = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Protected Sub btnVoltar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnVoltar.Click

        Try
            Response.Redirect("CustoFaturaLinha.aspx")
        Catch ex As Exception
            lblMsg.Text = ex.Message
        End Try

    End Sub

    Public Function FormatMediumDate(ByVal DateValue)

        Dim strYYYY
        Dim strMM
        Dim strDD

        'Strip the Year
        strYYYY = CStr(DatePart("yyyy", DateValue))

        'Strip the Month	
        strMM = CStr(DatePart("m", DateValue))

        'See if month is a single digit if so display it as 01, 02, 03, etc
        If Len(strMM) = 1 Then strMM = "0" & strMM

        'Strip the Date	
        strDD = CStr(DatePart("d", DateValue))

        'See if the Day is a single digit day if so diaply it as 01, 02, 03, etc
        If Len(strDD) = 1 Then strDD = "0" & strDD

        'return the date formatted as DD/MM/YYY	
        FormatMediumDate = strYYYY & "/" & strMM & "/" & strDD

        'you could return the date many ways just retype the above string
        'FormatMediumDate = strDD & "-" & strMM & "-" & strYYYY
        'FormatMediumDate = strMM & "/" & strDD & "/" & strYYYY
        'FormatMediumDate = strYYYY & "-" & strMM & "-" & strMM

    End Function

End Class
